package DB;

import Models.Vare;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class DBVare
{
	private Connection con;

	public DBVare()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	public ArrayList<Vare> getAllVare(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}

	public Vare findVare(String vareNummer, boolean retrieveAssociation)
	{
		String wClause = " id = '" + vareNummer + "'";
		return singleWhere(wClause, retrieveAssociation);
	}
	public ArrayList<Vare> searchVareName(String name, boolean retrieveAssociation)
	{
		String wClause = "navn like '%" + name + "%'";
		System.out.println("SøgVare " + wClause);
		return miscWhere(wClause, retrieveAssociation);
	}
	public int insertVare(Vare vare)
	{
		int rc = -1;
		String query="INSERT INTO Varer (navn, salgspris, minbeholdning, købspris, leverandør) VALUES ('";
		query += vare.getNavn() + "', " + vare.getSalgspris() + ", " + vare.getMinbeholdning() + ", " + vare.getKøbspris() + ", " + vare.getLeverandør() + ")";

		System.out.println("insert : " + query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex){
			System.out.println("Insert exception in vare db: "+ex.getMessage());
		}
		return rc;
	}

	public int updateVare(Vare vare)
	{
		Vare vareObj = vare;
		int rc=-1;

		String query="UPDATE " +
						"varer " +
					"SET " +
						"navn = '"+ vareObj.getNavn()+"', "+
						"salgspris = '"+vareObj.getSalgspris()+"', "+
						"minbeholdning = '"+vareObj.getMinbeholdning()+"', "+
						"købspris = '"+vareObj.getKøbspris()+"', "+
						"leverandør ='"+vareObj.getLeverandør()+"' "+
					"WHERE " +
						"id = '"+vareObj.getId()+"'";
		System.out.println("update : " + query);

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("fejl (updateVare): "+ex.getMessage());
		}
		return rc;
	}

	public int delete(String id)
	{
		int rc=-1;

		String query="DELETE FROM varer WHERE id = '"+
				id + "'";
		System.out.println("delete : " + query);

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("Delete exception in vare db: "+ex);
		}
		return rc;
	}

	private String buildQuery(String wClause)
	{
		String query="SELECT * FROM varer";

		if(wClause.length()>0)
			query+=" WHERE "+wClause;

		return query;
	}

	private Vare buildVare(ResultSet results)
	{
		Vare vareObj = new Vare();

		try
		{
			vareObj.setId(Integer.parseInt(results.getString("id")));
			vareObj.setNavn(results.getString("navn"));
			vareObj.setSalgspris(results.getDouble("salgspris"));
			vareObj.setOprindelsesland(results.getString("oprindelsesland"));
			vareObj.setKøbspris(results.getDouble("købspris"));
			vareObj.setBeskrivelse("");//results.getString("beskrivelse"));
			vareObj.setLeverandør(results.getInt("leverandør"));
			vareObj.setMinbeholdning(5);//results.getInt("minbeholdning"));
			vareObj.setUdlejningspris(44);//results.getDouble("udlejningspris"));
		}
		catch(Exception e)
		{
			System.out.println("fejl (buildVare): "+e.getMessage());
		}
		return vareObj;
	}

	private ArrayList<Vare> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList list = new ArrayList();

		String query = buildQuery(wClause);
		System.out.println("select(misc) : " + query);

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			int snr=0;
			while (results.next())
			{
				Vare vareObj = buildVare(results);
				list.add(vareObj);
			}
			stmt.close();
		}
		catch(Exception e)
		{
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}

	private Vare singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Vare vareObj = null;
		String query = buildQuery(wClause);
		System.out.println("select(single) : " + query);

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() )
			{
				vareObj = buildVare(results);
				stmt.close();
				if(retrieveAssociation)
				{
					//TODO: Leverandør handling!
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception: "+e);
		}
		return vareObj;
	}
}